package com.blueocean_health.care.management.mapper;

import com.blueocean_health.care.management.domain.base.OrderServiceNurseInfo;
import com.blueocean_health.care.management.domain.checktablevo.WorkersVo;
import com.blueocean_health.care.management.domain.dto.SelectCareSalaryDbDto;
import com.blueocean_health.care.management.domain.dto.SelectCareSalaryDto;
import com.blueocean_health.care.management.domain.dto.SelectNurseDto;
import com.blueocean_health.care.management.domain.dto.SelectNurseRespDto;
import com.blueocean_health.care.management.domain.dvo.OrderServiceNurseInfoVo;
import com.blueocean_health.care.management.util.MyMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.Date;
import java.util.List;
import java.util.Set;

@Mapper
public interface OrderServiceNurseInfoMapper extends MyMapper<OrderServiceNurseInfo> {
	
	/**
	 * 根据开始时间和结束时间获取在"工作"状态的护工
	 * @param startTime
	 * @param endTime
	 * @return
	 */
	List<OrderServiceNurseInfoVo> selectNurseByWorking(@Param("hospital") String hospital, @Param("startTime") String startTime, @Param("endTime") String endTime);

	/**
	 * 或者指定的护工服务信息
	 * @param selectCareSalaryDto
	 * @return
	 */
	List<SelectCareSalaryDbDto> selectNurseSalaryByTime(SelectCareSalaryDto selectCareSalaryDto);

	/**
	 * 获取护工表中符合条件的护工-不包括离职
	 * @param selectNurseDto
	 * @return
	 */
	List<SelectNurseRespDto> selectAllNurse(SelectNurseDto selectNurseDto);


	/**
	 * 获取护工表中符合条件的护工-包括离职
	 * @param selectNurseDto
	 * @return
	 */
	List<SelectNurseRespDto> selectAllNurseContainQuit(SelectNurseDto selectNurseDto);

	/**
	 *根据时间和科室id获取上班员工楼层。订单id等信息
	 * @param checkDate 2017-08-24 00:00:00
	 * @param deptid
	 */
	@Select("SELECT " +
			"t1.order_id AS orderId," +
			"t1.start_time," +
			"t1.end_time," +
			"t2.hospital_bed AS bedNumber," +
			"t4.`floor_name` AS floorName," +
			"t5.`building_name` AS buildingNumberName," +
			"t6.worker_name AS workerName," +
			"t6.`worker_id` AS workId," +
			"t6.`id` AS workerId," +
			"t7.start_time  AS stime," +
			"t7.end_time  AS etime " +
			"FROM " +
			"(" +
			"SELECT * FROM order_service_nurse_info a WHERE VERSION = (SELECT MAX(VERSION) FROM order_service_nurse_info WHERE a.order_id = order_id) " +
			") AS t1 " +
			"LEFT JOIN order_service_info t2 ON t1.service_id = t2.`service_id` " +
			"LEFT JOIN department t3 ON t2.`department`  = t3.`dept_id` " +
			"LEFT JOIN floor t4 ON t2.`floor` =  t4.`floor_id` " +
			"LEFT JOIN building t5 ON t2.`building_number` = t5.`building_id` " +
			"LEFT JOIN nurse_worker_info t6 ON t1.worker_id = t6.`worker_id` " +
			"LEFT JOIN " +
			"(SELECT * FROM `order_service_nurse_info` os WHERE VERSION = (SELECT MAX(VERSION) FROM order_service_nurse_info WHERE os.order_id = order_id) AND cancel = 0 AND `order_type` = 3  AND `service_type` != 4 ) " +
			"t7 ON  t7.refund_service_id = t1.service_id    " +
			"WHERE t1.start_time < #{nextDate} " +
            "AND t1.end_time > #{checkDate} " +
            "AND t3.`dept_id` =#{deptid} AND t1.cancel = 0 AND t2.`stflag` = 1 AND t1.order_type != 3 AND t1.service_type != 4  " +
			"AND t6.`hospital` =#{hospitalId} " +
			"GROUP BY workId")
	List<WorkersVo> getWorkQueryVoByDeptId(@Param("deptid") Integer deptid, @Param("checkDate") Date checkDate, @Param("nextDate") Date nextDate, @Param("hospitalId") String hospitalId);

	@Update("UPDATE order_service_nurse_info SET cancel=1 WHERE order_id=#{orderId}")
	void deleteOrder(@Param("orderId") String orderId);

	@Select("SELECT bb.`id` " +
            "FROM " +
            "order_service_nurse_info aa,nurse_worker_info bb " +
            "WHERE " +
            "aa.`version` =  " +
			"(SELECT MAX(VERSION) " +
            "FROM " +
            "order_service_nurse_info " +
            "WHERE aa.order_id = order_id) " +
            "AND aa.end_time > #{start} " +
            "AND aa.end_time <= #{end} " +
            "AND aa.cancel = 0  " +
            "AND aa.`worker_id`= bb.`worker_id` " +
            "AND bb.`hospital` = #{hospitalId} " +
			"AND aa.`order_type` != 3 " +
			"AND aa.`service_type` != 4 ")
    Set<Integer> getWorkIdByOrderEndTimeOfToday(@Param("start") Date start, @Param("end") Date end, @Param("hospitalId") String hospitalId);


	@Select("SELECT t1.id , t1.start_time,t1.end_time, t2.start_time AS stime,t2.end_time AS etime " +
			"FROM " +
			"(SELECT bb.`id` ,aa.`start_time`,aa.`end_time`,aa.service_id " +
			" FROM " +
			" order_service_nurse_info aa,nurse_worker_info bb " +
			" WHERE " +
			" aa.`version` =  " +
			" (SELECT MAX(VERSION) " +
			" FROM " +
			" order_service_nurse_info " +
			" WHERE aa.order_id = order_id) " +
			" AND aa.cancel = 0  " +
			" AND aa.`worker_id`= bb.`worker_id` " +
			" AND bb.`hospital` =#{hospitalId}" +
			" AND aa.`order_type` != 3 " +
			" AND aa.`service_type` != 4 " +
			" ) AS t1 " +
			"LEFT JOIN " +
			" (" +
			" SELECT * FROM `order_service_nurse_info` os WHERE VERSION = (SELECT MAX(VERSION) FROM order_service_nurse_info WHERE os.order_id = order_id) AND cancel = 0 AND `order_type` = 3  AND `service_type` != 4 " +
			" ) t2 " +
			"ON  t2.refund_service_id = t1.service_id  ")
	List<WorkersVo> getWorksByOrderEndTimeOfToday(@Param("hospitalId") String hospitalId);


	@Select("SELECT b.*,ori.`refund_time` ,ori.`is_active`" +
			"FROM " +
			"order_service_nurse_info b " +
			"LEFT JOIN order_refund_info ori ON b.`order_id` = ori.`order_id` " +
			"WHERE " +
			"b.VERSION = " +
			"(SELECT " +
			"MAX(VERSION) " +
			"FROM " +
			"order_service_nurse_info " +
			"WHERE b.order_id = order_id) " +
			"AND b.`cancel` = 0 and b.`order_type` != 3  and b.`service_type` != 4 ")
	List<OrderServiceNurseInfoVo> getAllMaxVersionEndtime();


	@Select("  SELECT t1.*,t2.start_time  AS refundStart,t2.end_time  AS refundEnd" +
            "  FROM" +
            "  (" +
            "  SELECT * FROM `order_service_nurse_info` os WHERE VERSION = (SELECT MAX(VERSION) FROM order_service_nurse_info WHERE os.order_id = order_id) " +
            "  AND cancel = 0 AND `order_type` != 3  AND `service_type` != 4" +
            "  ) t1" +
            "  LEFT JOIN" +
            "  (" +
            "  SELECT * FROM `order_service_nurse_info` os WHERE VERSION = (SELECT MAX(VERSION) FROM order_service_nurse_info WHERE os.order_id = order_id) " +
            "  AND cancel = 0 AND `order_type` = 3  AND `service_type` != 4" +
            "  ) t2 ON  t2.refund_service_id = t1.service_id" +
            "  LEFT JOIN nurse_worker_info t3 ON t3.`worker_id` = t1.worker_id" +
            "  WHERE" +
            "  t3.`hospital` =#{hospital}")
	List<OrderServiceNurseInfoVo> getAllMaxVersionEndtimeNew(String hospital);


	@Select("SELECT *FROM `order_service_nurse_info` b WHERE " +
            "b.VERSION = " +
            "(SELECT " +
            "MAX(VERSION) " +
            "FROM " +
            "order_service_nurse_info " +
            "WHERE b.order_id = order_id AND cancel = 0 AND service_type = 4)")
	List<OrderServiceNurseInfo> getAllMaxVersionGroupOrder();

	@Select("SELECT COUNT(*) FROM order_service_nurse_info b WHERE " +
			"b.VERSION = " +
			"(SELECT " +
			"MAX(VERSION) " +
			"FROM " +
			"order_service_nurse_info "+
			"WHERE b.order_id = order_id AND cancel = 0 "
			+ "AND service_type != 4 ) "
			+ "AND b.worker_id=#{workerId}")
	int getMaxVersionWorkerIdNumber(@Param("workerId") String workerId);


/*	@Select("SELECT * FROM order_service_nurse_info b WHERE " +7
			"b.VERSION = " +
			"(SELECT " +
			"MAX(VERSION) " +
			"FROM " +
			"order_service_nurse_info " +
			"WHERE b.order_id = order_id) AND b.cancel = 0 AND b.order_id = #{orderId}")
	OrderServiceNurseInfo getMaxVersionByOrderId(@Param("orderId")String orderId);	*/

	@Select("SELECT * FROM order_service_nurse_info b WHERE b.order_id = #{orderId} ORDER BY VERSION DESC LIMIT 1 ")
	OrderServiceNurseInfo getMaxVersionByOrderId(@Param("orderId") String orderId);
}